﻿Imports Microsoft.VisualBasic
Imports Devart.Data.Oracle
Imports DevExpress.XtraReports.UI
Imports DevExpress.XtraReports.Parameters
Imports rptUserRight
Imports System.Data

Public Class clsConnToDbByReport
    Dim objConn As OracleConnection
    Dim strConnString As String
    Dim xtrarp As XtraReport
    Dim objcmd As OracleCommand
    '  Dim dsAdmin As New dsAdminMaster


    Public Sub New()
        'Dim oConfig As System.Configuration.Configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/GHBankKYC")

        'If (oConfig.ConnectionStrings.ConnectionStrings.Count > 0) Then
        '    Try
        '        strConnString = oConfig.ConnectionStrings.ConnectionStrings("dsConnect").ConnectionString
        '    Catch

        '    End Try
        'Else
        '    Throw New ApplicationException("Could not load the database")
        'End If
        Try
            strConnString = ConfigurationManager.ConnectionStrings("dsConnect").ConnectionString
            ' strConnString = "Provider=OraOLEDB.Oracle;Data Source=GHBKYC;User Id=KYCADMIN;Password=oracle;Unicode=True"
        Catch
            Throw New ApplicationException("Could not load the database")
        End Try
    End Sub

    Public Function cn() As OracleConnection
        objConn = New OracleConnection
        If objConn.State = Data.ConnectionState.Closed Then
            With objConn
                .ConnectionString = strConnString
                .Open()
            End With
        End If
        Return objConn
    End Function


#Region "Administrator"

    Public Function rptUserRight(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "tblUserRight")
        With (rpt)
            .DataSource = ds.tblUserRight
            .DataMember = ds.tblUserRight.TableName
            .DataAdapter = dtAdapter
        End With


        Return True
    End Function

    Public Function rptDepartment(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "SDEPARTMENT")
        With (rpt)
            .DataSource = ds.SDEPARTMENT
            .DataMember = ds.SDEPARTMENT.TableName
            .DataAdapter = dtAdapter
        End With


        Return True
    End Function

    Public Function rptSystemConfig(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter
        objcmd = New OracleCommand
      

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "SSYSTEMCONFIG")

        With (rpt)
            .DataSource = ds.SSYSTEMCONFIG
            .DataMember = ds.SSYSTEMCONFIG.TableName
            .DataAdapter = dtAdapter
        End With


        Return True
    End Function

    Public Function rptZone(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "tblZone")
        With (rpt)
            .DataSource = ds.tblZone
            .DataMember = ds.tblZone.TableName
            .DataAdapter = dtAdapter
        End With


        Return True
    End Function

    Public Function rptUser(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "SUSER")
        With (rpt)
            .DataSource = ds.SUSER
            .DataMember = ds.SUSER.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptUnlockUser(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "H_UNLOCKUSER")
        With (rpt)
            .DataSource = ds.H_UNLOCKUSER
            .DataMember = ds.H_UNLOCKUSER.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptLoginlog(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "SLOGINLOG")
        With (rpt)
            .DataSource = ds.SLOGINLOG
            .DataMember = ds.SLOGINLOG.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptBranch(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAdminMaster, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand
        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "SBRANCH")
        With (rpt)
            .DataSource = ds.SBRANCH
            .DataMember = ds.SBRANCH.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

#End Region


#Region " Master AMLO "

    Public Function rptMAssetType(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MASSETTYPE")
        With (rpt)
            .DataSource = ds.MASSETTYPE
            .DataMember = ds.MASSETTYPE.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMObjType(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MOBJTYPE")
        With (rpt)
            .DataSource = ds.MOBJTYPE
            .DataMember = ds.MOBJTYPE.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMOfficeOcc(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MOFFICEOCC")
        With (rpt)
            .DataSource = ds.MOFFICEOCC
            .DataMember = ds.MOFFICEOCC.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMOfficeType(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MOFFICETYPE")
        With (rpt)
            .DataSource = ds.MOFFICETYPE
            .DataMember = ds.MOFFICETYPE.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMPsnRelation(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MPSNRELATION")
        With (rpt)
            .DataSource = ds.MPSNRELATION
            .DataMember = ds.MPSNRELATION.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMRelation(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MRELATION")
        With (rpt)
            .DataSource = ds.MRELATION
            .DataMember = ds.MRELATION.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptType_Id(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "TYPE_ID")
        With (rpt)
            .DataSource = ds.TYPE_ID
            .DataMember = ds.TYPE_ID.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMTscType1(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MTSCTYPE1")
        With (rpt)
            .DataSource = ds.MTSCTYPE1
            .DataMember = ds.MTSCTYPE1.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMTscType2(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MTSCTYPE2")
        With (rpt)
            .DataSource = ds.MTSCTYPE2
            .DataMember = ds.MTSCTYPE2.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMTscType3(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterAmlo, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MTSCTYPE3")
        With (rpt)
            .DataSource = ds.MTSCTYPE3
            .DataMember = ds.MTSCTYPE3.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function


#End Region


#Region " Master KYC "
    Public Function rptMBizRelation(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MBIZRELATION")
        With (rpt)
            .DataSource = ds.MBIZRELATION
            .DataMember = ds.MBIZRELATION.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMBusiness(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MBUSINESS")
        With (rpt)
            .DataSource = ds.MBUSINESS
            .DataMember = ds.MBUSINESS.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMCheckEvidence(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MCHECKEVIDENCE")
        With (rpt)
            .DataSource = ds.MCHECKEVIDENCE
            .DataMember = ds.MCHECKEVIDENCE.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMCustSubType(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MCUSTSUBTYPE")
        With (rpt)
            .DataSource = ds.MCUSTSUBTYPE
            .DataMember = ds.MCUSTSUBTYPE.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMMaritalStatus(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MMARITALSTATUS")
        With (rpt)
            .DataSource = ds.MMARITALSTATUS
            .DataMember = ds.MMARITALSTATUS.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMNationlity(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MNATIONALITY")
        With (rpt)
            .DataSource = ds.MNATIONALITY
            .DataMember = ds.MNATIONALITY.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMOccupation(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MOCCUPATION")
        With (rpt)
            .DataSource = ds.MOCCUPATION
            .DataMember = ds.MOCCUPATION.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMPosition(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MPOSITION")
        With (rpt)
            .DataSource = ds.MPOSITION
            .DataMember = ds.MPOSITION.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptMPrefix(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsMasterKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        'AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "MPREFIX")
        With (rpt)
            .DataSource = ds.MPREFIX
            .DataMember = ds.MPREFIX.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

#End Region


#Region " Sanction List "

    Public Function rptSanctionList(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsSanction, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "AMLLIST_HIST")
        With (rpt)
            .DataSource = ds.AMLLIST_HIST
            .DataMember = ds.AMLLIST_HIST.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptSanctionListHistory(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsSanctionNew, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "AMLLIST_HIST_NEW")
        With (rpt)
            .DataSource = ds.AMLO_NEW_IMPORT_TEMP
            .DataMember = ds.AMLO_NEW_IMPORT_TEMP.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptAmlWatchList(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsSanction, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "AMLWATCHLIST")
        With (rpt)
            .DataSource = ds.AMLWATCHLIST
            .DataMember = ds.AMLWATCHLIST.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptAmlWatchList2(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsSanctionNew, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "AMLWATCHLIST2")
        With (rpt)
            .DataSource = ds.AMLWATCHLIST2
            .DataMember = ds.AMLWATCHLIST2.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function
#End Region

#Region "AMLO"

    Public Function rptAMLO101(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "Amlo101")
        With (rpt)
            .DataSource = ds.Amlo101
            .DataMember = ds.Amlo101.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function
    Public Function rptAMLO102(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "Amlo102")
        With (rpt)
            .DataSource = ds.Amlo102
            .DataMember = ds.Amlo102.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function
    Public Function rptAMLO103(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "Amlo103")
        With (rpt)
            .DataSource = ds.Amlo103
            .DataMember = ds.Amlo103.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptAMLO1059(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "Amlo1059")
        With (rpt)
            .DataSource = ds.Amlo1059
            .DataMember = ds.Amlo1059.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptAMLO1059_s1(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "Amlo1059_s1")
        With (rpt)
            .DataSource = ds.Amlo1059_s1
            .DataMember = ds.Amlo1059_s1.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptAMLO1059_s2(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "Amlo1059_s2")
        With (rpt)
            .DataSource = ds.Amlo1059_s2
            .DataMember = ds.Amlo1059_s2.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptAMLO101_CBS(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "V_AMLO101_CBS")
        With (rpt)
            .DataSource = ds.V_AMLO101_CBS
            .DataMember = ds.V_AMLO101_CBS.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptAMLO102_CBS(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsAmloReport, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand

        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "V_AMLO102_CBS")
        With (rpt)
            .DataSource = ds.V_AMLO102_CBS
            .DataMember = ds.V_AMLO102_CBS.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

#End Region


    Private Sub AddParameter(ByVal clnDataColumn As Collection)
        Dim countCollection As Integer = clnDataColumn.Count
        Dim objClsVariable As clsVariable = New clsVariable
        With objcmd

            For Each Data As Object In clnDataColumn
                objClsVariable = Data

                If ("string".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OracleDbType.VarChar).Value = objClsVariable.valueColumn

                ElseIf ("integer".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OracleDbType.Integer).Value = objClsVariable.valueColumn

                ElseIf ("int32".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OracleDbType.Integer).Value = objClsVariable.valueColumn

                ElseIf ("double".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OracleDbType.Double).Value = objClsVariable.valueColumn

                ElseIf ("long".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OracleDbType.Double).Value = objClsVariable.valueColumn

                ElseIf ("datetime".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OracleDbType.Date).Value = objClsVariable.valueColumn

                End If

            Next

        End With
    End Sub

    Public Function rptDailyETLSync(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsDailyETLSync, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand
        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        ds.EnforceConstraints = False
        dtAdapter.Fill(ds, "dailyETLSync")

        With (rpt)
            .DataSource = ds.dailyETLSync
            .DataMember = ds.dailyETLSync.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptChkRiskDetail(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand
        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        ds.EnforceConstraints = False
        dtAdapter.Fill(ds, "mcustomer")

        With (rpt)
            .DataSource = ds.MCUSTOMER

            .DataMember = ds.MCUSTOMER.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptChkRiskAllCustomer(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsDailyETLSync, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand
        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        ds.EnforceConstraints = False
        dtAdapter.Fill(ds, "dailyETLSync")

        With (rpt)
            .DataSource = ds.dailyETLSync
            .DataMember = ds.dailyETLSync.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function
     
    Public Function rptCheckCustomer(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsDailyETLSync, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand
        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        ds.EnforceConstraints = False
        dtAdapter.Fill(ds, "dailyETLSync")

        With (rpt)
            .DataSource = ds.dailyETLSync
            .DataMember = ds.dailyETLSync.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function

    Public Function rptApprove(ByRef rpt As XtraReport, ByVal strSQL As String, ByRef ds As dsKYC, Optional ByVal clnDataColumn As Collection = Nothing) As Boolean
        Dim dtAdapter As New OracleDataAdapter

        objcmd = New OracleCommand
        With objcmd
            .Connection = cn()
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objcmd
        dtAdapter.Fill(ds, "dtHisApprove")
        With (rpt)
            .DataSource = ds.dtHisApprove
            .DataMember = ds.dtHisApprove.TableName
            .DataAdapter = dtAdapter
        End With

        Return True
    End Function



End Class
